import pandas as pd
import numpy as np
import os
from typing import List

data=pd.read_excel('综合信息查询_组合证券20200312_314.xls')
position=list(data['持仓'])
buypos=list(data['当日买量'])
stocks=list(data['证券代码'])
names=list(data['证券名称'])
mv=list(data['市值'])
price=list(data['最新价'])
account=list(data['组合编号'])
codeList=[]

for i in range(len(stocks)):
    if np.isnan(stocks[i]):
        continue
    code=int(stocks[i])
    pos=position[i]
    #pos=position[i]-buypos[i]
    #pos=np.floor(pos*1/100)*100
    codeStr=str(code).rjust(6,'0')
    acc=int(account[i])
    if ((codeStr<'009999')|((codeStr>='300000') & (codeStr<='309999'))):
        codeStr+='.SZ'
    elif ((codeStr>='600000') & (codeStr<='609999')):
        codeStr+='.SH'
    else:
        continue
    codeList.append({'code':codeStr,'account':acc,'hold':pos,'name':names[i],'marketValue':mv[i],'price':price[i]})
stocks314=pd.DataFrame(codeList)
#print(allStocks)




data=pd.read_excel('流通股20200410.xlsx')
position=list(data['流通量(万股)'])
stocks=list(data['代码'])
names=list(data['证券名称'])
holdList=[]

for i in range(len(stocks)):
    if np.isnan(stocks[i]):
        continue
        
    code=int(stocks[i])
    pos=position[i]*10000
    codeStr=str(code).rjust(6,'0')
    if ((codeStr<'009999')|((codeStr>='300000') & (codeStr<='309999'))):
        codeStr+='.SZ'
    elif ((codeStr>='600000') & (codeStr<='609999')):
        codeStr+='.SH'
    else:
        continue
    holdList.append({'code':codeStr,'name':names[i],'hold':pos})
stocksTotal=pd.DataFrame(holdList)
#print(stocksTotal)


check=[]
for code in list(stocks314['code'].unique()):
    pos314=stocks314[stocks314['code']==code]['hold'].sum()
    pos312=stocksTotal[stocksTotal['code']==code]['hold'].sum()
    name=stocks314[stocks314['code']==code]['name'].iloc[0]
    check.append({'code':code,'stocks':name,'312':pos312,'314':pos314,'ratio':np.round(pos314/pos312,4)})
check=pd.DataFrame(check)
#print(check)


data=pd.read_excel('持仓312_20200327.xlsx')
combi=list(data['组合编号'])
combiname=list(data['组合名称'])
stockholder=list(data['股东代码'])
stocks=list(data['证券代码'])
names=list(data['证券名称'])
market=list(data['交易市场'])
position=list(data['T日指令可用数量'])
priceList=list(data['最新价'])
modify=[]
for i in range(len(stocks)):
    if np.isnan(stocks[i]):
        continue
        
    code=int(stocks[i])
    pos=round(position[i]/3000)*1000
    codeStr=str(code).rjust(6,'0')
    seat=""
    if ((codeStr<'009999')|((codeStr>='300000') & (codeStr<='309999'))):
        codeStr+='.SZ'
        seat="061700"
    elif ((codeStr>='600000') & (codeStr<='609999')):
        codeStr+='.SH'
        seat="33211"
    else:
        continue

    modify.append({'code':codeStr,'name':names[i],'codeInt':code,'hold':pos,'makert':market[i],'stockholder':stockholder[i],'combi':combi[i],'combiname':combiname[i],'seat':seat,'price':priceList[i],'mv':priceList[i]*pos})
modify=pd.DataFrame(modify)
#modify.to_excel("change.xls",index=0,header=1)
#print(modify)


check2=[]

for code in list(stocksTotal['code'].unique()):
    pos314=stocks314[stocks314['code']==code]['hold'].sum()
    pos312=stocksTotal[stocksTotal['code']==code]['hold'].sum()
    modifyTo314=modify[modify['code']==code]['hold'].sum()
    name=stocksTotal[stocksTotal['code']==code]['name'].iloc[0]
    check2.append({'code':code,'stocks':name,'312':pos312,'314':pos314,'312To314':modifyTo314,'ratio':np.round(pos314/pos312,4),'ratio2':np.round((pos314+modifyTo314)/pos312,4),'modifyRatio':np.round((modifyTo314)/pos312,4)})
check2=pd.DataFrame(check2)
check2=check2[check2['ratio2']>0]
#check2.to_excel("modify312to314.xls",index=0,header=1)
#print(check2)

excluedCodes=list((set(check2[check2['ratio2']>0.4]['code'].unique())) | (set(check2[check2['312']<2000]['code'].unique())))
modify2=modify[~modify['code'].isin(excluedCodes)]
modify2.to_excel("change.xls",index=0,header=1)
check3=[]

for code in list(stocksTotal['code'].unique()):
    pos314=stocks314[stocks314['code']==code]['hold'].sum()
    pos312=stocksTotal[stocksTotal['code']==code]['hold'].sum()
    modifyTo314=modify2[modify2['code']==code]['hold'].sum()
    name=stocksTotal[stocksTotal['code']==code]['name'].iloc[0]
    check3.append({'code':code,'stocks':name,'312':pos312,'314':pos314,'312To314':modifyTo314,'ratio':np.round(pos314/pos312,4),'ratio2':np.round((pos314+modifyTo314)/pos312,4),'modifyRatio':np.round((modifyTo314)/pos312,4)})
check3=pd.DataFrame(check3)
check3=check3[check3['ratio2']>0]
check3.to_excel("modify312to314.xls",index=0,header=1)



